﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TodoApp.EntityFrameworkCore;
using TodoApp.ResultModel;
using Volo.Abp.DependencyInjection;
using Volo.Abp.Domain.Repositories.Dapper;
using Volo.Abp.EntityFrameworkCore;

namespace TodoApp.TMS_personnelmatters.Departments
{
    public class TMS_DepartureRepository : DapperRepository<TodoAppDbContext>, ITransientDependency, ITMS_DepartureRepository
    {
        public TMS_DepartureRepository(IDbContextProvider<TodoAppDbContext> dbContextProvider) : base(dbContextProvider)
        {
        }
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="tMS_Departure"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> CreateDeparture(TMS_Departure tMS_Departure)
        {
            var dbConnection = await GetDbConnectionAsync();
            return await dbConnection.ExecuteAsync($"insert into TMS_Departure values(NEWID(),'{tMS_Departure.Department_Id}','{tMS_Departure.Position_Id}','{tMS_Departure.Employees_Id}','{tMS_Departure.SuperiorResponsiblePerson}','{tMS_Departure.Departure_Date}','{tMS_Departure.Departure_Date}','{tMS_Departure.Departure_Why}','5B584655-4081-4B44-A63F-CF653AA78633',1,1,getdate())", null, await GetDbTransactionAsync());
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> DeleteDeparture(string id)
        {
            var dbConnection = await GetDbConnectionAsync();
            var count = 0;
            var ids = id.Split(',');
            foreach (var item in ids)
            {
                var sql = $"update TMS_Departure set Departure_Delete='false' where Id='{item}'";
                count += await dbConnection.ExecuteAsync(sql, null,
                 await GetDbTransactionAsync());
            }
            return count;
        }
        /// <summary>
        /// 显示
        /// </summary>
        /// <param name="name"></param>
        /// <param name="department"></param>
        /// <param name="position"></param>
        /// <param name="date"></param>
        /// <param name="dates"></param>
        /// <param name="state"></param>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<PageResponse<TMS_DepartureDto>> GetDeparture(string name, string department, string position, string date, string dates, int? state, int pageindex, int pagesize)
        {
            PageResponse<TMS_DepartureDto> response = new PageResponse<TMS_DepartureDto>();
            var dbConnection = await GetDbConnectionAsync();
            var sql = "select a.*,b.Department_Name,c.Position_Name,d.Employees_Name as name,e.Employees_Name as names,f.Employees_Name as namess from TMS_Departure a left join TMS_Department b on a.Department_Id=b.Id left join TMS_Position c on a.Position_Id=c.Id left join TMS_Employees d on a.Employees_Id=d.Id left join TMS_Employees e on a.SuperiorResponsiblePerson=e.Id left join TMS_Employees f on a.Departure_Approver=f.Id where a.Departure_Delete='true'";
            var list = await dbConnection.QueryAsync<TMS_DepartureDto>(sql, transaction: await GetDbTransactionAsync());
            response.PageTotal = list.Count();
            if (name != null)
            {
                sql += $" and d.Employees_Name like '%{name}%'";
            }
            if (department != null)
            {
                sql += $" and a.Department_Id = '{department}'";
            }
            if (position != null)
            {
                sql += $" and a.Position_Id = '{position}'";
            }
            if (date != null)
            {
                sql += $" and a.Departure_EntryDate = '{date}'";
            }
            if (dates != null)
            {
                sql += $" and a.Departure_Date = '{dates}'";
            }
            if (state != null)
            {
                sql += $" and a.Departure_State = '{state}'";
            }
            sql += $" order by a.Departure_DateTime offset {(pageindex - 1) * pagesize} rows fetch next {pagesize} rows only";
            var lists = await dbConnection.QueryAsync<TMS_DepartureDto>(sql, transaction: await GetDbTransactionAsync());
            response.PageData = lists.ToList();
            return response;
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="tMS_Departure"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> UpdateDeparture(TMS_Departure tMS_Departure)
        {
            var dbConnection = await GetDbConnectionAsync();
            return await dbConnection.ExecuteAsync($"update TMS_Departure set Department_Id='{tMS_Departure.Department_Id}',Position_Id='{tMS_Departure.Position_Id}',Employees_Id='{tMS_Departure.Employees_Id}',SuperiorResponsiblePerson='{tMS_Departure.SuperiorResponsiblePerson}',Departure_EntryDate='{tMS_Departure.Departure_EntryDate}',Departure_Date='{tMS_Departure.Departure_Date}',Departure_Why='{tMS_Departure.Departure_Why}' where Id='{tMS_Departure.Id}'", null, await GetDbTransactionAsync());
        }
        /// <summary>
        /// 提交
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> UpdateState(string id)
        {
            var dbConnection = await GetDbConnectionAsync();
            return await dbConnection.ExecuteAsync($"update TMS_Departure set Departure_State='2' where Id='{id}'", null, await GetDbTransactionAsync());
        }
    }
}
